"""
数据库管理模块
负责SQLite数据库的初始化、连接和所有CRUD操作
使用SQLite实现数据持久化存储
"""
import sqlite3
import os
import logging
from datetime import datetime, timedelta
import time

class DatabaseManager:
    def __init__(self, db_path="data/schedules.db"):
        self.db_path = db_path
        self.logger = logging.getLogger(__name__)
        self.init_database()
    
    def get_connection(self):
        """获取数据库连接"""
        try:
            conn = sqlite3.connect(self.db_path)
            conn.row_factory = sqlite3.Row  # 使返回结果像字典一样访问
            return conn
        except sqlite3.Error as e:
            self.logger.error(f"数据库连接失败: {e}")
            raise
    
    # 在 database.py 的 init_database 方法中增强健壮性
    def init_database(self):
        """初始化数据库和表结构"""
        max_retries = 3
        for attempt in range(max_retries):
            try:
                # 确保数据目录存在
                os.makedirs(os.path.dirname(self.db_path), exist_ok=True)
                
                conn = self.get_connection()
                cursor = conn.cursor()
                
                # 创建日程表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS schedules (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        title TEXT NOT NULL,
                        description TEXT,
                        start_time DATETIME NOT NULL,
                        end_time DATETIME,
                        priority INTEGER DEFAULT 1,
                        category TEXT DEFAULT '其他',
                        reminder_minutes INTEGER DEFAULT 15,
                        status TEXT DEFAULT 'pending',
                        created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_time DATETIME DEFAULT CURRENT_TIMESTAMP
                    )
                ''')

                # 创建提醒记录表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS reminder_logs (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        schedule_id INTEGER,
                        reminder_time DATETIME,
                        status TEXT,
                        FOREIGN KEY (schedule_id) REFERENCES schedules (id)
                    )
                ''')
                
                # 创建用户设置表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS user_settings (
                        key TEXT PRIMARY KEY,
                        value TEXT
                    )
                ''')
                
                # 创建索引以提高查询性能
                cursor.execute('CREATE INDEX IF NOT EXISTS idx_schedules_start_time ON schedules(start_time)')
                cursor.execute('CREATE INDEX IF NOT EXISTS idx_schedules_status ON schedules(status)')
                cursor.execute('CREATE INDEX IF NOT EXISTS idx_reminder_logs_schedule_id ON reminder_logs(schedule_id)')

                
                # 创建其他表...
                conn.commit()
                self.logger.info("数据库初始化成功")
                break  # 成功则跳出重试循环
                
            except sqlite3.Error as e:
                self.logger.error(f"数据库初始化失败 (尝试 {attempt+1}/{max_retries}): {e}")
                if attempt == max_retries - 1:
                    raise  # 最后一次重试失败后抛出异常
                time.sleep(1)  # 等待1秒后重试
            finally:
                if 'conn' in locals():
                    conn.close()

    # def init_database(self):
    #     """初始化数据库和表结构"""
    #     try:
    #         # 确保数据目录存在
    #         os.makedirs(os.path.dirname(self.db_path), exist_ok=True)
            
    #         conn = self.get_connection()
    #         cursor = conn.cursor()
            
    #         # 创建日程表
    #         cursor.execute('''
    #             CREATE TABLE IF NOT EXISTS schedules (
    #                 id INTEGER PRIMARY KEY AUTOINCREMENT,
    #                 title TEXT NOT NULL,
    #                 description TEXT,
    #                 start_time DATETIME NOT NULL,
    #                 end_time DATETIME,
    #                 priority INTEGER DEFAULT 1,
    #                 category TEXT DEFAULT '其他',
    #                 reminder_minutes INTEGER DEFAULT 15,
    #                 status TEXT DEFAULT 'pending',
    #                 created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    #                 updated_time DATETIME DEFAULT CURRENT_TIMESTAMP
    #             )
    #         ''')
            
    #         # 创建提醒记录表
    #         cursor.execute('''
    #             CREATE TABLE IF NOT EXISTS reminder_logs (
    #                 id INTEGER PRIMARY KEY AUTOINCREMENT,
    #                 schedule_id INTEGER,
    #                 reminder_time DATETIME,
    #                 status TEXT,
    #                 FOREIGN KEY (schedule_id) REFERENCES schedules (id)
    #             )
    #         ''')
            
    #         # 创建用户设置表
    #         cursor.execute('''
    #             CREATE TABLE IF NOT EXISTS user_settings (
    #                 key TEXT PRIMARY KEY,
    #                 value TEXT
    #             )
    #         ''')
            
    #         # 创建索引以提高查询性能
    #         cursor.execute('CREATE INDEX IF NOT EXISTS idx_schedules_start_time ON schedules(start_time)')
    #         cursor.execute('CREATE INDEX IF NOT EXISTS idx_schedules_status ON schedules(status)')
    #         cursor.execute('CREATE INDEX IF NOT EXISTS idx_reminder_logs_schedule_id ON reminder_logs(schedule_id)')
            
    #         conn.commit()
    #         self.logger.info("数据库初始化成功")
            
    #     except sqlite3.Error as e:
    #         self.logger.error(f"数据库初始化失败: {e}")
    #         raise
    #     finally:
    #         if 'conn' in locals():
    #             conn.close()
    
    
    def add_schedule(self, title, start_time, description="", end_time=None, 
                    priority=1, category="其他", reminder_minutes=15):
        """添加新日程"""
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            cursor.execute('''
                INSERT INTO schedules 
                (title, description, start_time, end_time, priority, category, reminder_minutes)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            ''', (title, description, start_time, end_time, priority, category, reminder_minutes))
            
            schedule_id = cursor.lastrowid
            conn.commit()
            self.logger.info(f"添加日程成功: {title} (ID: {schedule_id})")
            return schedule_id
            
        except sqlite3.Error as e:
            self.logger.error(f"添加日程失败: {e}")
            return None
        finally:
            if 'conn' in locals():
                conn.close()
    
    def get_schedule(self, schedule_id):
        """根据ID获取单个日程"""
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            cursor.execute('SELECT * FROM schedules WHERE id = ?', (schedule_id,))
            row = cursor.fetchone()
            
            if row:
                return dict(row)
            else:
                return None
                
        except sqlite3.Error as e:
            self.logger.error(f"获取日程失败: {e}")
            return None
        finally:
            if 'conn' in locals():
                conn.close()
    
    def get_schedules_by_date(self, date):
        """获取指定日期的所有日程"""
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT * FROM schedules 
                WHERE DATE(start_time) = DATE(?)
                ORDER BY start_time
            ''', (date,))
            
            schedules = [dict(row) for row in cursor.fetchall()]
            return schedules
            
        except sqlite3.Error as e:
            self.logger.error(f"获取日程失败: {e}")
            return []
        finally:
            if 'conn' in locals():
                conn.close()
    
    def get_all_schedules(self, limit=None, offset=0):
        """获取所有日程（可分页）"""
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            if limit:
                cursor.execute('''
                    SELECT * FROM schedules 
                    ORDER BY start_time DESC
                    LIMIT ? OFFSET ?
                ''', (limit, offset))
            else:
                cursor.execute('''
                    SELECT * FROM schedules 
                    ORDER BY start_time DESC
                ''')
            
            schedules = [dict(row) for row in cursor.fetchall()]
            return schedules
            
        except sqlite3.Error as e:
            self.logger.error(f"获取所有日程失败: {e}")
            return []
        finally:
            if 'conn' in locals():
                conn.close()
    
    # 时间处理方法
    def _convert_datetime(self, dt_str):
        """转换数据库时间字符串为datetime对象"""
        if isinstance(dt_str, datetime):
            return dt_str
        
        formats = [
            '%Y-%m-%d %H:%M:%S',
            '%Y-%m-%d %H:%M',
            '%Y-%m-%d'
        ]
        
        for fmt in formats:
            try:
                return datetime.strptime(dt_str, fmt)
            except ValueError:
                continue
        
        # 如果无法解析，返回当前时间
        self.logger.warning(f"无法解析时间字符串: {dt_str}，使用当前时间")
        return datetime.now()

    # 在 get_upcoming_schedules 方法中使用
    def get_upcoming_schedules(self, hours=24):
        """获取未来指定小时内的日程"""
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            now = datetime.now()
            future_time = now + timedelta(hours=hours)
            
            # 使用字符串格式进行比较，避免时区问题
            now_str = now.strftime('%Y-%m-%d %H:%M:%S')
            future_str = future_time.strftime('%Y-%m-%d %H:%M:%S')
            
            cursor.execute('''
                SELECT * FROM schedules 
                WHERE start_time BETWEEN ? AND ?
                AND status = 'pending'
                ORDER BY start_time
            ''', (now_str, future_str))
            
            schedules = [dict(row) for row in cursor.fetchall()]
            return schedules
            
        except sqlite3.Error as e:
            self.logger.error(f"获取即将到来日程失败: {e}")
            return []
        finally:
            if 'conn' in locals():
                conn.close()

    # def get_upcoming_schedules(self, hours=24):
    #     """获取未来指定小时内的日程"""
    #     try:
    #         conn = self.get_connection()
    #         cursor = conn.cursor()
            
    #         now = datetime.now()
    #         future_time = now + timedelta(hours=hours)
            
    #         cursor.execute('''
    #             SELECT * FROM schedules 
    #             WHERE start_time BETWEEN ? AND ?
    #             AND status = 'pending'
    #             ORDER BY start_time
    #         ''', (now, future_time))
            
    #         schedules = [dict(row) for row in cursor.fetchall()]
    #         return schedules
            
    #     except sqlite3.Error as e:
    #         self.logger.error(f"获取即将到来日程失败: {e}")
    #         return []
    #     finally:
    #         if 'conn' in locals():
    #             conn.close()
    
    def update_schedule(self, schedule_id, **kwargs):
        """更新日程信息"""
        try:
            if not kwargs:
                self.logger.warning("没有提供更新字段")
                return False
            
            conn = self.get_connection()
            cursor = conn.cursor()
            
            # 构建更新语句
            set_clause = ", ".join([f"{key} = ?" for key in kwargs.keys()])
            values = list(kwargs.values())
            values.append(schedule_id)
            
            cursor.execute(f'''
                UPDATE schedules 
                SET {set_clause}, updated_time = CURRENT_TIMESTAMP
                WHERE id = ?
            ''', values)
            
            conn.commit()
            self.logger.info(f"更新日程成功: {schedule_id}")
            return cursor.rowcount > 0
            
        except sqlite3.Error as e:
            self.logger.error(f"更新日程失败: {e}")
            return False
        finally:
            if 'conn' in locals():
                conn.close()
    
    def update_schedule_status(self, schedule_id, status):
        """更新日程状态"""
        return self.update_schedule(schedule_id, status=status)
    
    def delete_schedule(self, schedule_id):
        """删除日程"""
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            # 先删除相关的提醒记录
            cursor.execute('DELETE FROM reminder_logs WHERE schedule_id = ?', (schedule_id,))
            
            # 再删除日程
            cursor.execute('DELETE FROM schedules WHERE id = ?', (schedule_id,))
            
            conn.commit()
            self.logger.info(f"删除日程: {schedule_id}")
            return cursor.rowcount > 0
            
        except sqlite3.Error as e:
            self.logger.error(f"删除日程失败: {e}")
            return False
        finally:
            if 'conn' in locals():
                conn.close()
    
    def search_schedules(self, keyword, limit=50):
        """搜索包含关键字的日程"""
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            search_pattern = f"%{keyword}%"
            cursor.execute('''
                SELECT * FROM schedules 
                WHERE title LIKE ? OR description LIKE ?
                ORDER BY start_time DESC
                LIMIT ?
            ''', (search_pattern, search_pattern, limit))
            
            schedules = [dict(row) for row in cursor.fetchall()]
            return schedules
            
        except sqlite3.Error as e:
            self.logger.error(f"搜索日程失败: {e}")
            return []
        finally:
            if 'conn' in locals():
                conn.close()
    
    def log_reminder(self, schedule_id):
        """记录提醒日志"""
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            cursor.execute('''
                INSERT INTO reminder_logs (schedule_id, reminder_time, status)
                VALUES (?, ?, ?)
            ''', (schedule_id, datetime.now(), 'shown'))
            
            conn.commit()
            self.logger.debug(f"记录提醒日志: {schedule_id}")
            return True
            
        except sqlite3.Error as e:
            self.logger.error(f"记录提醒日志失败: {e}")
            return False
        finally:
            if 'conn' in locals():
                conn.close()
    
    def get_reminder_logs(self, schedule_id=None, limit=100):
        """获取提醒日志"""
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            if schedule_id:
                cursor.execute('''
                    SELECT * FROM reminder_logs 
                    WHERE schedule_id = ?
                    ORDER BY reminder_time DESC
                    LIMIT ?
                ''', (schedule_id, limit))
            else:
                cursor.execute('''
                    SELECT * FROM reminder_logs 
                    ORDER BY reminder_time DESC
                    LIMIT ?
                ''', (limit,))
            
            logs = [dict(row) for row in cursor.fetchall()]
            return logs
            
        except sqlite3.Error as e:
            self.logger.error(f"获取提醒日志失败: {e}")
            return []
        finally:
            if 'conn' in locals():
                conn.close()
    
    def get_statistics(self):
        """获取日程统计信息"""
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            stats = {}
            
            # 总日程数
            cursor.execute('SELECT COUNT(*) as total FROM schedules')
            stats['total'] = cursor.fetchone()[0]
            
            # 按状态统计
            cursor.execute('''
                SELECT status, COUNT(*) as count 
                FROM schedules 
                GROUP BY status
            ''')
            stats['by_status'] = {row[0]: row[1] for row in cursor.fetchall()}
            
            # 按优先级统计
            cursor.execute('''
                SELECT priority, COUNT(*) as count 
                FROM schedules 
                GROUP BY priority
            ''')
            stats['by_priority'] = {row[0]: row[1] for row in cursor.fetchall()}
            
            # 按类别统计
            cursor.execute('''
                SELECT category, COUNT(*) as count 
                FROM schedules 
                GROUP BY category
            ''')
            stats['by_category'] = {row[0]: row[1] for row in cursor.fetchall()}
            
            # 今日日程数
            today = datetime.now().strftime("%Y-%m-%d")
            cursor.execute('SELECT COUNT(*) FROM schedules WHERE DATE(start_time) = DATE(?)', (today,))
            stats['today'] = cursor.fetchone()[0]
            
            return stats
            
        except sqlite3.Error as e:
            self.logger.error(f"获取统计信息失败: {e}")
            return {}
        finally:
            if 'conn' in locals():
                conn.close()
    
    def backup_database(self, backup_path=None):
        """备份数据库"""
        try:
            if backup_path is None:
                backup_dir = "data/backups"
                os.makedirs(backup_dir, exist_ok=True)
                timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
                backup_path = os.path.join(backup_dir, f"db_backup_{timestamp}.db")
            
            # 使用SQLite的备份API
            source_conn = self.get_connection()
            backup_conn = sqlite3.connect(backup_path)
            
            with backup_conn:
                source_conn.backup(backup_conn)
            
            backup_conn.close()
            source_conn.close()
            
            self.logger.info(f"数据库备份成功: {backup_path}")
            return backup_path
            
        except sqlite3.Error as e:
            self.logger.error(f"数据库备份失败: {e}")
            return None

# 测试函数
def test_database():
    """测试数据库功能"""
    import tempfile
    
    # 使用临时文件测试
    temp_db = tempfile.NamedTemporaryFile(delete=False, suffix='.db')
    temp_db.close()
    
    try:
        # 初始化数据库
        db = DatabaseManager(temp_db.name)
        print("数据库初始化成功")
        
        # 测试添加日程
        schedule_id = db.add_schedule(
            title="测试日程",
            start_time="2024-01-01 10:00:00",
            description="这是一个测试日程",
            priority=2,
            category="测试"
        )
        print(f"添加日程成功，ID: {schedule_id}")
        
        # 测试查询日程
        schedule = db.get_schedule(schedule_id)
        print(f"查询日程: {schedule['title']}")
        
        # 测试更新日程
        db.update_schedule_status(schedule_id, "completed")
        print("更新日程状态成功")
        
        # 测试搜索日程
        results = db.search_schedules("测试")
        print(f"搜索到 {len(results)} 个相关日程")
        
        # 测试统计信息
        stats = db.get_statistics()
        print(f"日程统计: {stats}")
        
        print("所有数据库测试通过!")
        
    except Exception as e:
        print(f"数据库测试失败: {e}")
    finally:
        # 清理临时文件
        if os.path.exists(temp_db.name):
            os.unlink(temp_db.name)

if __name__ == "__main__":
    # 配置日志
    logging.basicConfig(level=logging.INFO)
    
    # 测试数据库功能
    test_database()